If you need to remove fragmentation from your database indexes, you can proceed with the following steps.
To remove fragmentation
Run the scripts below to find fragmentation and generate the alter statements for removing fragmentation. This script generates the following:
Alter statements to rebuild indexes which have over 30% fragmentation and are over 1000 pages
Alter statements to reorganize indexes which have less than 30% fragmentation and are over 1000 pages
The recommended limit is 1000 pages. You can set your own limit, like 700 or even 500. The lower the limit, the more indexes will be updated.
SELECT
'ALTER INDEX ' + i.name + ' ON ' + o.name + ' REBUILD with(online=on, maxdop=1);',
p.avg_fragmentation_in_percent AS frag
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') p
JOIN sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN sys.objects AS o ON p.object_id = o.object_id
AND p.index_type_desc != 'EXTENDED INDEX'
WHERE p.index_id > 0 AND p.avg_fragmentation_in_percent > 30.0 and p.page_count >1000
ORDER By frag desc
SELECT
'ALTER INDEX ' + i.name + ' ON ' + o.name + ' REORGANIZE;',
p.avg_fragmentation_in_percent AS frag
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') p
JOIN sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN sys.objects AS o ON p.object_id = o.object_id
AND p.index_type_desc != 'EXTENDED INDEX'
WHERE p.index_id > 0 AND p.avg_fragmentation_in_percent > 10.0 AND p.avg_fragmentation_in_percent <= 30.0 and p.page_count >1000
ORDER By frag desc
The previous scripts do not include spatial indexes. You can’t rebuild spatial indexes online. When a spatial index being rebuilt, the underlying table is unavailable because the spatial index holds the schema lock. You should rebuild spatial indexes only during off-peak hours.
The following scripts generate the necessary statements for removing fragmentation on spatial indexes:
SELECT
'ALTER INDEX ' + i.name + ' ON ' + o.name + ' REBUILD with(maxdop=1);',
p.avg_fragmentation_in_percent AS frag
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') p
JOIN sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN sys.objects AS o ON p.object_id = o.object_id
AND p.index_type_desc = 'EXTENDED INDEX'
WHERE p.index_id > 0 AND p.avg_fragmentation_in_percent > 30.0 and p.page_count >1000
AND i.type_desc='SPATIAL'
ORDER By frag desc
SELECT
'ALTER INDEX ' + i.name + ' ON ' + o.name + ' REORGANIZE;',
p.avg_fragmentation_in_percent AS frag
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED') p
JOIN sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id
JOIN sys.objects AS o ON p.object_id = o.object_id
AND p.index_type_desc = 'EXTENDED INDEX'
WHERE p.index_id > 0 AND p.avg_fragmentation_in_percent > 10.0 AND p.avg_fragmentation_in_percent <= 30.0 and p.page_count >1000
AND i.type_desc='SPATIAL'
ORDER By frag desc
Review the statements that are generated.
Execute the output statements one by one, or in groups (for example, in groups of 5 statements at a time). If you execute all the statements at once, the system may hang for a long time.